<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Queries</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Ruby, SQLite, queries, database, sqlite3">
<meta name="description" content="In this part of the SQLite Ruby tutorial, we
perform several queries.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 

<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Queries</h1>

<p>
We have already established a connection to the database. Now we 
are going modify and fetch the data from the database. 
</p>

<div class="center">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* top_horizontal */
google_ad_slot = "3327173442";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
Data is retrieved from the database with the SELECT statement. In SQLite Ruby module, first
we prepare the SQL statement with the <code>prepare</code> method. The SQL string
is sent to the database engine, which checks the statement validity, syntax and in
some databases also the user permissions to perform certain queries. If all is OK, a
statement object is returned to the Ruby script. The next step
is the call to the <code>execute</code> method. The method executes the query
within the database. The data is retrieved. 
</p>

<p>
The Ruby SQLite module has several methods to fetch data from database tables. 
After the SQL statement was prepared and executed, we can go through the returned data.
</p>


<h2>Fetching data</h2>

<p>
In the first example we fetch one row from the Cars table.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.new "test.db"
    
    id = 1
    
    stm = db.prepare "SELECT * FROM Cars WHERE Id=?"
    stm.bind_param 1, id
    rs = stm.execute
    
    row = rs.next
    
    puts row.join "\s"
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    stm.close if stm
    db.close if db
end
</pre>

<p>
In the example we do all the steps to get the first row
from the Cars table.
</p>

<pre class="explanation">
stm = db.prepare "SELECT * FROM Cars WHERE Id=?"
</pre>

<p>
The SELECT statement is prepared with the <code>prepare</code> 
method. A statement object is returned. 
</p>

<pre class="explanation">
stm.bind_param 1, id
</pre>

<p>
A parameter is bound to the placeholder in the statement.
</p>

<pre class="explanation">
rs = stm.execute
</pre>

<p>
The statement is executed. A ResultSet object is returned. 
</p>

<pre class="explanation">
row = rs.next
</pre>

<p>
Obtains the next row from the result set. Since we want to 
fetch only one row, we call the <code>next</code> method
once. 
</p>

<pre class="explanation">
puts row.join "\s"
</pre>

<p>
The row is a Ruby array. The three fields are joined with a
space character to form a line using the <code>join</code> method.
</p>

<pre>
$ ./fetch.rb
1 Audi 52642
</pre>

<p>
Example output.
</p>

<hr class="btm">

<p>
In the following example, we will fetch five rows. 
We put the <code>next</code> method in a while loop. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
        
    stm = db.prepare "SELECT * FROM Cars LIMIT 5"
    rs = stm.execute
    
    while (row = rs.next) do
        puts row.join "\s"
    end
        
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    stm.close if stm
    db.close if db
end
</pre>

<p>
In this script we connect to the database and fetch 5 rows
of the Cars table.
</p>

<pre class="explanation">
stm = db.prepare "SELECT * FROM Cars LIMIT 5"
</pre>

<p>
This is the SQL statement for fetching 5 rows. 
</p>

<pre class="explanation">
while (row = rs.next) do
    puts row.join "\s"
end
</pre>

<p>
The <code>next</code> method is put inside the 
while loop. It returns the next row from the result set.
If there is no more row left, the method returns nil and
the while loop is terminated. 
</p>


<hr class="btm">

<p>
We can get data from the result set using the <code>each</code>
method.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
        
    stm = db.prepare "SELECT * FROM Cars LIMIT 5"
    rs = stm.execute
    
    rs.each do |row|
        puts row.join "\s"
    end
            
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    stm.close if stm
    db.close if db
end
</pre>

<p>
Again we select five rows from the Cars table. 
</p>

<pre class="explanation">
rs.each do |row|
    puts row.join "\s"
end
</pre>

<p>
We use the <code>each</code> method to iterate over
the result set. 
</p>


<hr class="btm">

<p>
In the next example, we will use the <code>execute</code> method
of the database object. It is a convenience method that saves
a few keystrokes. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
        
    rows = db.execute "SELECT * FROM Cars LIMIT 5"
        
    for row in rows do
        puts row.join "\s"
    end
            
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
The example selects and prints five rows from the Cars table.
</p>

<pre class="explanation">
rows = db.execute "SELECT * FROM Cars LIMIT 5"
</pre>

<p>
Here we do two jobs in one step. We prepare the statement and
execute it. The method returns the data in a Ruby array.
</p>

<pre class="explanation">
for row in rows do
    puts row.join "\s"
end
</pre>

<p>
We print the data from the Ruby array. 
</p>

<hr class="btm">

<p>
So far we have seen data returned in the form of a
ResultSet or an array. The next example will return the
data in the form of an array of hashes. This way we
can identify field values by their column names. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
    db.results_as_hash = true
        
    ary = db.execute "SELECT * FROM Cars LIMIT 5"    
        
    ary.each do |row|
        printf "%s %s %s\n", row['Id'], row['Name'], row['Price']
    end
             
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
In the example we get fields by their column names. 
</p>

<pre class="explanation">
db.results_as_hash = true
</pre>

<p>
We set the <code>results_as_hash</code> property to true. 
All rows will be returned as Hash objects, with the column names 
as the keys. 
</p>

<pre class="explanation">
ary.each do |row|
    printf "%s %s %s\n", row['Id'], row['Name'], row['Price']
end
</pre>

<p>
We get the fields by their column names.
</p>

<pre>
$ ./fetch_hash.rb
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
</pre>

<p>
Example output.
</p>

<h2>Fetching a row or a value</h2>

<p>
Ruby SQLite module has two convenience methods for retrieving a 
row or a value. In the first example, we will get a single row
from a table. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
      
    row = db.get_first_row "SELECT * FROM Cars WHERE Id=1"       
    puts row.join "\s"
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
We get the data for the first row of the Cars table. 
</p>

<pre class="explanation">
row = db.get_first_row "SELECT * FROM Cars WHERE Id=1" 
</pre>

<p>
The <code>get_first_row</code> method gets the first row
and discards all possible other rows. 
</p>

<pre class="explanation">
puts row.join "\s"
</pre>

<p>
The row is printed to the console. 
</p>

<pre>
$ ./fetchrow.rb
1 Audi 52642
</pre>

<p>
Output of the fetchrow.rb example.
</p>


<hr class="btm">

<p>
In the last example, we select a single value.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
      
    val = db.get_first_value "SELECT Price FROM Cars WHERE Name='Bentley'"       
    puts val
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
We select a price for a specific car. 
</p>

<pre class="explanation">
val = db.get_first_value "SELECT Price FROM Cars WHERE Name='Bentley'"      
</pre>

<p>
With the <code>get_first_value</code> method we select a specific field
of a row. In our case it is the price of the Bentley car. 
</p>

<pre>
$ ./fetchvalue.rb
350000    
</pre>

<p>
Output.
</p>

<p>
In this part of the SQLite Ruby tutorial, we have demonstrated how to fetch data
from the database using various methods. 
</p>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>

<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 12, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>
